package com.ratingbay.console.naivequery;

import java.util.List;
import java.util.ArrayList;
import java.util.HashMap;
import java.math.BigDecimal;
 
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.Persistence;
import javax.persistence.Query;
import javax.persistence.EntityTransaction;

import com.ratingbay.console.model.Game;
import com.ratingbay.console.model.GameSummary;

public class GameSummaryNaiveQuery{
	private GameSummaryNaiveQuery(){
	}
	private static GameSummaryNaiveQuery self = null;
    public static GameSummaryNaiveQuery getInstance(){
    	if(null == self){
    		self = new GameSummaryNaiveQuery();
    	}
        return self;
    }


    //for cjob
    public List<Long> getAllIdGames(){
    	EntityManagerFactory emf = NaiveQuery.getInstance().getEntityManagerFactory();
        EntityManager em = emf.createEntityManager();

        String sql = "SELECT id FROM game";
        Query query =  em.createNativeQuery(sql);
        
        return query.getResultList();
    } 

    public Long getTweetCount(Long idGame){
    	EntityManagerFactory emf = NaiveQuery.getInstance().getEntityManagerFactory();
        EntityManager em = emf.createEntityManager();

        String sql = "select count(*) from tweet where id_game = " + idGame;
        Query query =  em.createNativeQuery(sql);

        List<Long> res = (List<Long>) query.getResultList();
        if(res.size() > 0){
        	return res.get(0);
        }
        return null;
    }

    public Long getIdMostPositiveTweet(Long idGame){
    	EntityManagerFactory emf = NaiveQuery.getInstance().getEntityManagerFactory();
        EntityManager em = emf.createEntityManager();

        String sql = "select id from tweet where id_game = " + idGame + " and sentiment_type = 1 order by sentiment_probability desc limit 1";
        Query query =  em.createNativeQuery(sql);

        List<Long> res = (List<Long>) query.getResultList();
        if(res.size() > 0){
        	return res.get(0);
        }
        return null;
    }

    public Long getIdMostNegativeTweet(Long idGame){
    	EntityManagerFactory emf = NaiveQuery.getInstance().getEntityManagerFactory();
        EntityManager em = emf.createEntityManager();

        String sql = "select id from tweet where id_game = " + idGame + " and sentiment_type = 2 order by sentiment_probability desc limit 1";
        Query query =  em.createNativeQuery(sql);

        List<Long> res = (List<Long>) query.getResultList();
        if(res.size() > 0){
        	return res.get(0);
        }
        return null;
    }

    public Long getIdMostRetweetTweet(Long idGame){
    	EntityManagerFactory emf = NaiveQuery.getInstance().getEntityManagerFactory();
        EntityManager em = emf.createEntityManager();

        String sql = "select id from tweet where id_game = " + idGame + " order by retweet_count desc limit 1";
        Query query =  em.createNativeQuery(sql);

        List<Long> res = (List<Long>) query.getResultList();
        if(res.size() > 0){
        	return res.get(0);
        }
        return null;
    }

    public BigDecimal getSentimentScore(Long idGame) {
    	EntityManagerFactory emf = NaiveQuery.getInstance().getEntityManagerFactory();
        EntityManager em = emf.createEntityManager();

       // String sql = "select score from sentiment_score_history where id_game = " + idGame + " order by insert_time desc limit 1";
        String sql = "select score from game where id = " + idGame;
        Query query =  em.createNativeQuery(sql);

        List<BigDecimal> res = (List<BigDecimal>) query.getResultList();
        if(res.size() > 0){
        	return res.get(0);
        }
        return null;
    }
    
    //cjob entry
    public void assembleGameSummary(List<Long> idGames) {
        System.out.println("CJOB start assembleGameSummary");

		EntityManagerFactory emf = NaiveQuery.getInstance().getEntityManagerFactory();
        EntityManager em = emf.createEntityManager();

        String sql = null;
        Query query =  null;

    	for(int i=0; i< idGames.size(); i++) {
			Long gameId = idGames.get(i);
			String tSql = "select count(*) from game_summary where id_game = " + gameId;
			query = em.createNativeQuery(tSql);
			Long isInserted = (Long)query.getSingleResult();
			if(1 > isInserted){
				sql = "insert into game_summary (id_game,id_most_positive_tweet,id_most_negative_tweet,id_most_retweet_tweet,tweet_count,sentiment_score) values ("+ gameId + "," + getIdMostPositiveTweet(gameId) + "," + getIdMostNegativeTweet(gameId) + "," + getIdMostRetweetTweet(gameId) + "," + getTweetCount(gameId) + "," + getSentimentScore(gameId) + ")"; 
			}else{
				sql = "update game_summary set id_most_positive_tweet = " + getIdMostPositiveTweet(gameId) + ",id_most_negative_tweet = " + getIdMostNegativeTweet(gameId) + ",id_most_retweet_tweet = " + getIdMostRetweetTweet(gameId) + ",tweet_count = " + getTweetCount(gameId) + ",sentiment_score = " + getSentimentScore(gameId) + " where id_game = " + gameId;
			}
			EntityTransaction tx = em.getTransaction();
			tx.begin();
			query = em.createNativeQuery(sql);
			query.executeUpdate();
			tx.commit();
		}
        
        System.out.println("CJOB end assembleGameSummary");
    }
}